package com.example.bootshardingjdbc.mapper;

import com.example.bootshardingjdbc.entity.User;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;
import org.springframework.stereotype.Service;

import java.util.List;

@Repository
public interface UserMapper {

    @Insert("insert into t_user(nickname,password,sex,birthday,age) values(#{nickname},#{password},#{sex},#{birthday},#{age})")
    void addUser(User user);

    @Select("select * from t_user where age = 18")
    List<User> findUsers();

  /*  @Select("select * from t_user where sex = 1")
    List<User> findUsers();*/


    //相对支持的 SQL 样例
    /**
     * 功能描述: 支持子查询
     * @Auther: Solming
     **/
    @Select("SELECT count(*) FROM (SELECT * FROM t_user t where  age = 18 and sex = #{sex}) t")
    Long supportCount(@Param("sex")Integer sex);

    //冗余括号就不能用
    @Select("SELECT count(*) FROM ((SELECT * FROM t_user t where  age = 18 and sex = #{sex})) t")
    Long errorCount(@Param("sex")Integer sex);


    /**
     * 功能描述: ============================支持子查询
     * @Auther: Solming
     **/
    @Select("SELECT count(*) FROM (SELECT * FROM t_user WHERE id in (SELECT id from t_user where sex = 1 and  age = 18) )t")
    Long supportCount1();
    /**
     * 功能描述:error
     * @Auther: Solming
     * @Date:  2022/2/18
     * @param: []
     * @return: java.lang.Long
     **/
    //不完整的分片字段
    @Select("SELECT count(*) FROM (SELECT * FROM t_user WHERE id in (SELECT id from t_user where sex = 1 ) )t")
    Long errorCount1();
    //分片字段规则取模，不能为字符串
    @Select("SELECT count(*) FROM (SELECT * FROM t_user WHERE id in (SELECT id from t_user where sex = 1 and  age = #{age}) )t")
    Long errorCount2(@Param("age")String age);


    //=========================子查询 END======================


    //====================group By ============================


    @Select("SELECT count(sex) FROM t_user group by sex")
    List<Long> groupBy1();


    @Select("SELECT count(sex) sex_alias FROM t_user group by sex having sex_alias > 2 ")
    List<Long> errorGroupBy1();

    @Select("SELECT count(sex) sex_alias FROM t_user group by sex having sex_alias > 2 ")
    List<Long> errorGroupBy2();


    @Select("SELECT count(birthday) as  birth_alias  FROM t_user group by birthday where sex = 1 having birth_alias > 2 order by sex ")
    List<Long> errorGroupBy3();

    //============ Group  BY  END===================

    //============= DISTINCT ===============

    //可行
    @Select("select distinct sex from t_user")
    List<User> distinctSql1();


    //sum 和 distinct 组合使用不行。
    @Select("select sum(distinct nickname) from t_user")
    List<User> distinctSql2();

    //分开可以用。
    @Select("select distinct nickname,sum(sex) as sex from t_user group by nickname")
    List<User> errorDistinctSql2();

}
